<?php
/**
 * Klasse zum sammeln und analysieren von Benutzer angeklickte regionen
 * zur Erfolgskontrolle
 * 
 * @author		Joachim Ruf
 * @copyright	Copyright (c) 2003-2013 Loresoft Software (http://www.loresoft.de)
 * @license		http://yapary.loresoft.de/lizenzbedingungen     New BSD License
 * 
 * */


Class Yapary_Marketing_HeatmapAdmin
{
    private $oSql = NULL;
    
    
    function __construct (Yapary_Db_Sql_Connector $_oSql)
    {
        $this->oSql = $_oSql;
    }
    

    
    /**
     * Gibt die gesammelten Daten als Liste aus
     * @param string $_dateFrom				: Anfangsdatum
     * @param string $_dateTo (optional)	: Enddatum
     * @return string
     * */
    public function display ( $_dateFrom, $_dateTo = NULL)
    {
        $result = '';
        
        if ($_dateTo == NULL) {
            $_dateTo = $_dateFrom;
        }
        
        $oDbResult = $this->oSql->request("SELECT *
        					  FROM heatmap
        					  WHERE DATE(DateTime) >= :dateFrom AND DATE(DateTime) <= :dateTo
        					  ORDER BY DateTime",
                             array('dateFrom' => $_dateFrom,
                                   'dateTo'   => $_dateTo));

        if ($oDbResult->getCount() >= 1) {
            $i = $oDbResult->getCount();
            while ($row = $oDbResult->getNext()) {
                $result .= '<tr>
                				<td>'.$i--.'</td>
                				<td>'.$row->Ip.'</td>
                				<td>'.$row->Dns.'</td>
                				<td>'.$row->Agent.'</td>
                				<td>'.$row->SiteName.'</td>
                				<td>'.$row->DateTime.'</td>
                			</tr>';
            }
            
            $result  = '<table border="1" cellspacing="0" cellpadding="3" width="100%">
            			<tr>
            				<th>#</th>
            				<th>IP</th>
            				<th>DNS</th>
            				<th>Agent</th>
            				<th>SiteName</th>
            				<th>DateTime</th>
            			</tr>
            			'.$result.'
            			</table>';
        }
        
        return $result;
    }
    
    
    

    
    /**
     * Gibt die besuchten Seiten prozentual als Liste aus
     * @param string $_dateFrom				: Anfangsdatum
     * @param string $_dateTo (optional)	: Enddatum
     * @return string
     * */
    public function displaySiteName ( $_dateFrom, $_dateTo = NULL)
    {
        $result = '';
        
        if ($_dateTo == NULL) {
            $_dateTo = $_dateFrom;
        }
        
        $oDbResult = $this->oSql->request("SELECT
        						SiteName,
        						COUNT(SiteName) AS Counter,
        						(COUNT(SiteName) / total.CounterAll * 100) AS Percent
        					  FROM
        					  	heatmap,
        					  	(SELECT COUNT(*) AS CounterAll FROM heatmap WHERE DATE(DateTime) >= :dateFrom AND DATE(DateTime) <= :dateTo) AS total
        					  WHERE DATE(DateTime) >= :dateFrom AND DATE(DateTime) <= :dateTo
        					  GROUP BY SiteName
        					  ORDER BY Counter DESC",
                             array('dateFrom' => $_dateFrom,
                                   'dateTo'   => $_dateTo));

        if ($oDbResult->getCount() >= 1) {
            $i = $oDbResult->getCount();
            while ($row = $oDbResult->getNext()) {
                $result .= '<tr>
                				<td>'.$row->SiteName.'</td>
                				<td>'.$row->Counter.'</td>
                				<td>'.$row->Percent.'</td>
            				</tr>';
            }
            
            $result  = '<table border="1" cellspacing="0" cellpadding="3" width="100%">
            			<tr>
            				<th>SiteName</th>
            				<th>Count</th>
            				<th>%</th>
        				</tr>
            			'.$result.'
            			</table>';
        }
        
        return $result;
    }

}